package org.opsli.modulars.business.iot.protocol;

import com.alibaba.fastjson.JSON;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.FileInputStream;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

public class ExcelData {
    private XSSFSheet sheet;

    /**
     * 构造函数，初始化excel数据
     * @param filePath  excel路径
     * @param sheetName sheet表名
     */
    ExcelData(String filePath,String sheetName){
        FileInputStream fileInputStream = null;
        try {
            fileInputStream = new FileInputStream(filePath);
            XSSFWorkbook sheets = new XSSFWorkbook(fileInputStream);
            //获取sheet
            sheet = sheets.getSheet(sheetName);
        } catch (Exception e) {
            e.printStackTrace();
        }
    }

    /**
     * 根据行和列的索引获取单元格的数据
     * @param row
     * @param column
     * @return
     */
    public String getExcelDateByIndex(int row,int column){
        XSSFRow row1 = sheet.getRow(row);
        String cell = row1.getCell(column).toString();
        return cell;
    }

    /**
     * 根据某一列值为“******”的这一行，来获取该行第x列的值
     * @param caseName
     * @param currentColumn 当前单元格列的索引
     * @param targetColumn 目标单元格列的索引
     * @return
     */
    public String getCellByCaseName(String caseName,int currentColumn,int targetColumn){
        String operateSteps="";
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            XSSFRow row = sheet.getRow(i);
            String cell = row.getCell(currentColumn).toString();
            if(cell.equals(caseName)){
                operateSteps = row.getCell(targetColumn).toString();
                break;
            }
        }
        return operateSteps;
    }

    //打印excel数据
    public void readExcelData(){
        //获取行数
        int rows = sheet.getPhysicalNumberOfRows();
        for(int i=0;i<rows;i++){
            //获取列数
            XSSFRow row = sheet.getRow(i);
            int columns = row.getPhysicalNumberOfCells();
            for(int j=0;j<columns;j++){
                String cell = row.getCell(j).toString();
                System.out.println(cell);
            }
        }
    }

    //测试方法
    public static void main(String[] args){
       /* ExcelData sheet1 = new ExcelData("D:\\上街资料\\1234.xlsx", "Sheet2");
        List<Map> list=new ArrayList<Map>();
        for(int i=0;i<=16;i++){
            //名称
            String cell1=sheet1.getExcelDateByIndex(i, 1);
            //规格
            String cell2=sheet1.getExcelDateByIndex(i, 4);
            //材质
            String cell3=sheet1.getExcelDateByIndex(i, 5);
            //长度
            String cell4=sheet1.getExcelDateByIndex(i, 7);
            Map map=new HashMap<>();
            map.put("data1",cell1);
            map.put("data2",0.35);
            map.put("data3",cell3);
            map.put("data4",cell4);
            map.put("data5",cell2);
            list.add(map);
        }
        System.out.println(JSON.toJSONString(list));*/
       /* ExcelData sheet1 = new ExcelData("D:\\上街资料\\调压设备.xlsx", "调压柜");
        List<Map> list=new ArrayList<Map>();
        for(int i=2;i<=43;i++){
            //名称
            String cell1=sheet1.getExcelDateByIndex(i, 0);
            //规格
            String cell2=sheet1.getExcelDateByIndex(i, 1);
            Map map=new HashMap<>();
            map.put("data1",cell1);
            map.put("data2",cell2.replace(".0",""));
            map.put("data3",cell1);
            list.add(map);
        }
        System.out.println(JSON.toJSONString(list));*/

        ExcelData sheet1 = new ExcelData("D:\\上街资料\\12345.xlsx", "Sheet3");
        List<Map> list=new ArrayList<Map>();
        for(int i=0;i<=49;i++){
            //名称
            String cell1=sheet1.getExcelDateByIndex(i, 6);
            //压力
            String cell2=sheet1.getExcelDateByIndex(i, 10);
            //流量
            String cell3=sheet1.getExcelDateByIndex(i, 11);
            //累计气量
            String cell4=sheet1.getExcelDateByIndex(i, 12);
            Map map=new HashMap<>();
            map.put("data1",cell1);
            map.put("data2",cell2);
            map.put("data3",cell3);
            map.put("data4",cell4);
            list.add(map);
        }
        System.out.println(JSON.toJSONString(list));
    }
}

